package cn.lidar.util;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

public class ExcelUtils {
    public static void main(String[] args) throws Exception {
        File file = new File("D:\\csv\\a.csv");
        XSSFWorkbook workbook = getWorkbook(file);
        int numberOfSheets = workbook.getNumberOfSheets();  // sheet 的个数
        for (int i = 0; i < numberOfSheets; i++) {  // 遍历每一个 sheet
            XSSFSheet sheet = workbook.getSheetAt(i);
            String sheetName1 = workbook.getSheetName(i);   // sheet 名
            String sheetName2 = sheet.getSheetName();       // sheet 名
            int firstRowNum = sheet.getFirstRowNum();   // 第一行所在行数, zero based
            int lastRowNum = sheet.getLastRowNum();     // 最后一行所在行数
            for (int j = firstRowNum; j < lastRowNum; j++) {    // 遍历每一行
                XSSFRow row = sheet.getRow(j);
                short firstCellNum = row.getFirstCellNum(); // 该行第一列所在列数, zero based
                short lastCellNum = row.getLastCellNum();   // 该行最后一列所在列数
                for (int k = firstCellNum; k < lastCellNum; k++) {
                    XSSFCell cell = row.getCell(k); // 第 i 个表, 第 j 行, 第 k 列的 Cell 对象
                    CellType cellType = cell.getCellType(); // 获取当前单元格类型
                    String cellStringValue = getCellStringValue(cell);  // 自己封装的读取方法
                }
            }
            System.out.println(sheetName1);
        }
    }

    public static XSSFWorkbook getWorkbook(File file) throws IOException {
        InputStream is = new FileInputStream(file);
        XSSFWorkbook workbook = getWorkbook(is);
        is.close();
        return workbook;
    }

    public static XSSFWorkbook getWorkbook(InputStream is) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        workbook.setMissingCellPolicy(Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        return workbook;
    }

    /**
     * 根据 Cell 的类型读取 Cell 的值，如果类型不是 CellType.NUMERIC
     * 或 CellType.STRING 或 CellType.BOOLEAN，则返回空串。
     *
     * @param cell 要读取的 Cell
     * @return Cell 的值
     */
    static String getCellStringValue(Cell cell) {
        String str = "";
        switch (cell.getCellType()) {
            case NUMERIC:
                str = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING:
                str = cell.getStringCellValue();
                break;
            case BOOLEAN:
                str = String.valueOf(cell.getBooleanCellValue());
                break;
        }
        return str;
    }
}
